package com.study.spark.scala.excel

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.types._

/**
 * Excel操作
 *
 * @author stephen
 * @date 2019-10-12 17:05
 */
object ExcelDemo {

  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .master("local[*]")
      .appName("Excel Demo")
      .getOrCreate()

    import spark.implicits._

    val df = spark.createDataset(Seq((1, "zhangsan", 20), (2, "lisi", 30), (3, "wangwu", 40)))
      .toDF("id", "name", "age")

    // 写excel
    df.write
      .format("com.crealytics.spark.excel")
      .option("dataAddress", "'sheet_test'!A1")
      .option("useHeader", "true")
      //.option("dateFormat", "yy-mmm-d") // Optional, default: yy-m-d h:mm
      //.option("timestampFormat", "mm-dd-yyyy hh:mm:ss") // Optional, default: yyyy-mm-dd hh:mm:ss.000
      //.mode("append") // Optional, default: overwrite.
      .save("./People.xlsx")


    // 读excel
    val peopleSchema = StructType(Array(
      StructField("id", LongType, nullable = false),
      StructField("name", StringType, nullable = false),
      StructField("age", IntegerType, nullable = false)))

    val df2 = spark.read
      .format("com.crealytics.spark.excel")
      .option("sheetName", "sheet_test")
      .option("useHeader", "true")
      .schema(peopleSchema)
      .load("./People.xlsx")

    df2.show(false)
  }
}
